﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using NHibernate;
using com.pro2e.console.core.hibernate;

/// <summary>
/// SqlHelper 的摘要描述
/// </summary>
/// 
namespace com.pro2e.console.generic.util
{
    public class NHibernateSqlHelper
    {               
        private SqlCommand command;        
        
        public NHibernateSqlHelper()
        {           
            command = new SqlCommand();
            command.Connection = (SqlConnection)NHibernateSessionManager.Instance.GetSession().Connection;
            
            if (HttpContext.Current.Items["ITransaction"] != null) 
            {
                ITransaction itx = (ITransaction)HttpContext.Current.Items["ITransaction"];
                itx.Enlist(command);
            }                                    
        }

        public NHibernateSqlHelper(IDbConnection conn, ITransaction itx)
        {            
            command = new SqlCommand();
            command.Connection = (SqlConnection)conn; 
            itx.Enlist(command);
        }   

        public static SqlParameter ToSqlParameter(string parameterName, object value)
        {
            if (value == null)
            {
                return new SqlParameter(parameterName, DBNull.Value);
            }
            else
            {
                return new SqlParameter(parameterName, value);
            }
        }

        public int ExecuteNonQuery(string strSQL)
        {
            int ret = 0;
            command.CommandText = strSQL;
            ret = command.ExecuteNonQuery();
            return ret;
        }

        public object ExecuteScalar(string strSQL)
        {
            object ret = null;
            command.CommandText = strSQL;
            ret = command.ExecuteScalar();
            return ret;
        }

        public DataTable GetDataTable(string strSQL)
        {
            DataTable DT = new DataTable();
            command.CommandText = strSQL;

            SqlDataAdapter DataAdapter = new SqlDataAdapter(command);
            DataAdapter.Fill(DT);
            return DT;
        }

      

        private void AttachParams(ref SqlCommand cmd, SqlParameter[] paras)
        {
            foreach (SqlParameter para in paras)
            {
                if (para.Value == null)
                {
                    para.Value = System.DBNull.Value;
                }
                cmd.Parameters.AddWithValue(para.ParameterName, para.Value);
            }
        }

        /// <summary>
        /// 執行Commit並再開啟一個Session
        /// </summary>
        /// <returns></returns>
        public static NHibernateSqlHelper DoCommit()
        {
            NHibernateSessionManager.Instance.GetSession().Transaction.Commit();

            ITransaction ix = NHibernateSessionManager.Instance.GetSession().BeginTransaction();

            SqlConnection sqlConn =  (SqlConnection)NHibernateSessionManager.Instance.GetSession().Connection;

            return (new NHibernateSqlHelper(sqlConn, ix));
        }


          /// <summary>
        /// 執行sql指令傳回Table
        /// </summary>
        /// <param name="strSQL">執行的SQL</param>
        /// <param name="firstResult">第一筆的位置</param>
        /// <param name="maxRecord">最多取幾筆</param>
        /// <returns></returns>
        public DataTable GetDataTable(string strSQL, int firstResult, int maxRecord)
        {
            command.CommandText = strSQL;
            IDataReader dataReader = command.ExecuteReader();

            DataTable dt;
            dt = new DataTable();
            int colCount = dataReader.FieldCount;
            for (int i = 0; i < colCount; i++)
            {
                dt.Columns.Add(new DataColumn(dataReader.GetName(i), dataReader.GetFieldType(i)));
            }

            object[] vald = new object[colCount];
            int iCount = 0;
            while (dataReader.Read() && maxRecord > 0)
            {
                if (iCount >= firstResult)
                {
                    for (int i = 0; i < colCount; i++)
                        vald[i] = dataReader.GetValue(i);
                    dt.Rows.Add(vald);
                    maxRecord--;
                }
                iCount++;
            }

            if (!dataReader.IsClosed)
            {
                dataReader.Close();
                dataReader.Dispose();
            }
          
            return dt;
        }
    }
}